package com.tools.utils;

import com.tools.constants.Constant;
import com.tools.entity.TableAttributeEntity;
import com.tools.entity.TableContent;
import com.tools.model.DBType;
import com.tools.model.DataBaseModel;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库工具
 *
 * @author < a href=" ">ZhangYang</ a>
 * @version v1.0.01
 * @date 2018-08-01 14:23:38
 */

public class DBUtil {
  private static final int DB_CONNECTION_TIMEOUTS_SECONDS = 1;

  /**
   * 获得数据库连接
   *
   * @param config
   *
   * @return
   *
   * @throws ClassNotFoundException
   * @throws SQLException
   */
  public static Connection getConnection(DataBaseModel config) throws ClassNotFoundException, SQLException {
    DriverManager.setLoginTimeout(DB_CONNECTION_TIMEOUTS_SECONDS);
    DBType dbType = DBType.valueOf(config.getDbType());
    Class.forName(dbType.getDriverClass());
    String url = getConnectionURL(config);
    if (dbType == DBType.Oracle) {
      Connection connection;
      try {
        connection = DriverManager.getConnection(url, config.getUserName(), config.getUserPwd());
      } catch (Exception e) {
        String oracle = String
          .format(DBType.OracleServiceName.getConnectionUrlPattern(), config.getConnURL(), config.getListenPort(),
                  config.getDbName()
          );
        connection = DriverManager.getConnection(oracle, config.getUserName(), config.getUserPwd());
      }
      return connection;
    } else {
      return DriverManager.getConnection(url, config.getUserName(), config.getUserPwd());
    }
  }

  /**
   * 获得数据库连接URL
   *
   * @param dbConfig
   *
   * @return
   *
   * @throws ClassNotFoundException
   */
  public static String getConnectionURL(DataBaseModel dbConfig) throws ClassNotFoundException {
    DBType dbType = DBType.valueOf(dbConfig.getDbType());
    String connectionRUL = String
      .format(dbType.getConnectionUrlPattern(), dbConfig.getConnURL(), dbConfig.getListenPort(), dbConfig.getDbName(),
              dbConfig.getEncoding()
      );
    return connectionRUL;
  }

  /**
   * 获得数据库的表名
   *
   * @param config
   *
   * @return
   *
   * @throws Exception
   */
  public static List<String> getTableNames(DataBaseModel config) throws Exception {
    Connection conn = getConnection(config);
    List<String> tables = new ArrayList<>();
    ResultSet rs;
    if (config.getDbType().equalsIgnoreCase(Constant.SQL_SERVER)) {
      // 如果是sqlserver数据库通过查询获得所有表跟视图
      String sql = "select name from sysobjects  where xtype='u' or xtype='v' ";
      rs = conn.createStatement().executeQuery(sql);
      while (rs.next()) {
        tables.add(rs.getString("name"));
      }

    } else {
      // 如果非sqlserver类型的数据库通过JDBC获得所有表跟视图
      DatabaseMetaData md = conn.getMetaData();
      String[] types = {"TABLE", "VIEW"};
      if (config.getDbType().equalsIgnoreCase(Constant.POSTGRE_SQL)) {
        rs = md.getTables(null, null, null, types);
      } else {
        String catalog = conn.getCatalog() == null ? null : conn.getCatalog();
        rs = md.getTables(catalog, config.getUserName().toUpperCase(), "%%", types);
      }
      while (rs.next()) {
        tables.add(rs.getString(3));
      }
    }
    return tables;
  }

  /**
   * 获得指定表的属性
   *
   * @param config
   * @param tableName
   *
   * @return
   *
   * @throws Exception
   */
  public static TableContent getTableAttribute(DataBaseModel config, String tableName) throws Exception {
    Connection conn = getConnection(config);
    TableContent content = new TableContent();
    ResultSet rs;
    DatabaseMetaData md = conn.getMetaData();
    String[] types = {"TABLE", "VIEW"};
    if (config.getDbType().equalsIgnoreCase(Constant.POSTGRE_SQL)) {
      rs = md.getTables(null, null, tableName, types);
    } else {
      String catalog = conn.getCatalog() == null ? null : conn.getCatalog();
      rs = md.getTables(catalog, config.getUserName().toUpperCase(), tableName, types);
    }
    if (rs.next()) {
      content.setTableCat(rs.getString("TABLE_CAT"));
      content.setTableSchem(rs.getString("TABLE_SCHEM"));
      content.setTableName(rs.getString("TABLE_NAME"));
      content.setTableType(rs.getString("TABLE_TYPE"));
      content.setRemarks(rs.getString("REMARKS"));
      content.setTypeCat(rs.getString("TYPE_CAT"));
      content.setTypeSchem(rs.getString("TYPE_SCHEM"));
      content.setTypeName(rs.getString("TYPE_NAME"));
      content.setSelfReferencingColName(rs.getString("SELF_REFERENCING_COL_NAME"));
      content.setRefGeneration(rs.getString("REF_GENERATION"));
    }
    return content;
  }

  /**
   * 获取表的列属性
   *
   * @param config 数据库配置文件
   * @param tableName 表名
   *
   * @return
   *
   * @throws Exception
   */
  public static List<TableAttributeEntity> getTableColumns(DataBaseModel config, String tableName) throws Exception {
    Connection conn = getConnection(config);
    DatabaseMetaData md = conn.getMetaData();

    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    if (config.getDbType().equalsIgnoreCase(Constant.MYSQL)) {

      PreparedStatement stmt = conn.prepareStatement("select *  from " + tableName + " where 1=0 ");
      ResultSet resultSet = stmt.executeQuery();
      rsmd = resultSet.getMetaData();
    } else {
      rs = md.getColumns(null, null, tableName, null);
    }
    int n = rsmd.getColumnCount();
    Map<String, TableAttributeEntity> columnMap = new HashMap<>();
    for (int i = 1; i <= n; i++) {
      String colName = rsmd.getColumnName(i);
      String dataType = rsmd.getColumnClassName(i);
      if ("java.math.BigDecimal".equals(dataType) && rsmd.getScale(i) == 0) {
        dataType = "Long";
      }
      if ("oracle.sql.CLOB".equals(dataType)) {
        dataType = "String";
      }
      TableAttributeEntity attr = new TableAttributeEntity();
      attr.setTdColumnName(colName);
      attr.setTdJavaType(dataType.endsWith("Timestamp") || dataType.endsWith("Date") ? "java.util.Date" : dataType);
      rs = md.getColumns(conn.getCatalog(), "%%", tableName, "%%");
      while (rs.next()) {
        if (colName.equals(rs.getString("COLUMN_NAME"))) {
          attr.setRemarks(rs.getString("REMARKS"));
          attr.setColumnDef(rs.getString("COLUMN_DEF"));
          attr.setTdJdbcType(rs.getString("TYPE_NAME"));
          attr.setColumnSize(rs.getInt("COLUMN_SIZE"));
          attr.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
          attr.setOrdinalPosition(rs.getInt("ORDINAL_POSITION"));
          attr.setNullable(rs.getInt("NULLABLE") == 1 ? true : false);
        }
      }
      columnMap.put(colName, attr);
    }
    if (columnMap.size() == 0) {
      throw new NullPointerException("从表中获取字段失败!获取不到任何字段!");
    }
    ArrayList<TableAttributeEntity> result = new ArrayList<>(columnMap.values());
    Collections.sort(result);
    return result;
  }

  /**
   * 获得主键名称
   *
   * @param config
   * @param tableName
   *
   * @return
   *
   * @throws Exception
   */
  public static String getTablePrimaryKey(DataBaseModel config, String tableName) throws Exception {
    Connection conn = getConnection(config);
    DatabaseMetaData md = conn.getMetaData();
    ResultSet rs = null;
    if (config.getDbType().equalsIgnoreCase(Constant.MYSQL)) {
      rs = md.getPrimaryKeys(conn.getCatalog(), conn.getSchema(), tableName);
    } else {
      rs = md.getPrimaryKeys(null, null, tableName);
    }
    while (rs.next()) {
      return rs.getString("COLUMN_NAME");
    }
    return null;
  }

}
